import sys
import pandas as pd
from PyQt5 import QtWidgets, QtGui
from untitled import Ui_MainWindow
from PyQt5.QtWidgets import QFileDialog, QMainWindow, QApplication, QTableWidgetItem

pd.set_option("display.unicode.ambiguous_as_wide", True)
pd.set_option("display.unicode.east_asian_width", True)
pd.set_option("display.width", 240)  # 设置打印宽度(**重要**)


class MyPyQT_Form(QMainWindow, Ui_MainWindow):
    df = 0
    df1 = 0
    df2 = 0

    def __init__(self):
        super(MyPyQT_Form, self).__init__()
        self.setupUi(self)

    def openfile(self):
        openfile_name = QFileDialog.getOpenFileName(
            self, "选择文件", "", "Excel files(*.xlsx , *.xls)"
        )
        self.df = pd.read_excel(openfile_name[0])
        # 第一种方法取行数与列数
        # row_num = df.shape[0]  # 取行数
        # col_num = df.shape[1]  # 取列数

        # 第二种方法取行数与列数
        row_num = self.df.index.size
        col_num = self.df.columns.size

        self.tableWidget.setColumnCount(col_num)
        self.tableWidget.setRowCount(row_num)
        for i in range(row_num):
            for j in range(col_num):
                temp_db_a2 = self.df.iloc[i, j]
                db_a2 = QTableWidgetItem(str(temp_db_a2))
                self.tableWidget.setItem(i, j, db_a2)

        col_name3 = list(self.df.columns)
        self.tableWidget.setHorizontalHeaderLabels(col_name3)

    def openfile_1(self):
        openfile_name = QFileDialog.getOpenFileName(
            self, "选择文件", "", "Excel files(*.xlsx , *.xls)"
        )
        self.df1 = pd.read_excel(openfile_name[0], dtype={"单位编号": object})

        # self.df1['开始时间'] = self.df1['开始时间'].dt.strftime('%Y-%m-%d')
        # self.df1['开始时间'] = self.df1['开始时间'].apply(str)
        # 第一种方法取行数与列数
        # row_num = df.shape[0]  # 取行数
        # col_num = df.shape[1]  # 取列数

        # 第二种方法取行数与列数
        row_num = self.df1.index.size
        col_num = self.df1.columns.size

        self.tableWidget_2.setColumnCount(col_num)
        self.tableWidget_2.setRowCount(row_num)
        for i in range(row_num):
            for j in range(col_num):
                temp_db_a2 = self.df1.iloc[i, j]

                db_a2 = QTableWidgetItem(str(temp_db_a2))
                self.tableWidget_2.setItem(i, j, db_a2)

        col_name3 = list(self.df1.columns)
        self.tableWidget_2.setHorizontalHeaderLabels(col_name3)

    def add(self):

        self.df2 = pd.merge(left=self.df1, right=self.df, on="单位编号", how="left")
        row_num = self.df2.index.size
        col_num = self.df2.columns.size

        self.tableWidget_3.setColumnCount(col_num)
        self.tableWidget_3.setRowCount(row_num)
        for i in range(row_num):
            for j in range(col_num):
                temp_db_a2 = self.df2.iloc[i, j]

                db_a2 = QTableWidgetItem(str(temp_db_a2))
                self.tableWidget_3.setItem(i, j, db_a2)

        col_name3 = list(self.df2.columns)
        self.tableWidget_3.setHorizontalHeaderLabels(col_name3)

    def export(self):
        # print(self.df2.dtypes)
        # 直接导出会发现个人编号是科学计数法，上面代码查看发现个人编号的类型为int64,其他的为object
        # self.df2.to_excel("export.xlsx")
        self.df2["个人编号"]=self.df2["个人编号"].astype("str")
        self.df2.to_excel("export.xlsx")


if __name__ == "__main__":
    import sys

    app = QtWidgets.QApplication(sys.argv)
    myshow = MyPyQT_Form()
    myshow.show()
    sys.exit(app.exec_())
